/**
 * 版权所有 2009-2015 中国中医科学院中医药信息研究所 何前锋 frontpeak@yahoo.com.cn
 **/
package cn.ac.cintcm.sh.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;

import cn.ac.cintcm.sh.domain.Herb;
import cn.ac.cintcm.sh.domain.Rule;
import cn.ac.cintcm.sh.domain.Unit;
import cn.ac.cintcm.sh.domain.View;

public class ViewDao {
    
    private HsqldbDao dao = (new HsqldbDao()).getDao();
    
    public List<View> getViews() {
        List<View> views = new ArrayList<View>();
        String sql="select id, name from views";
        dao.openConnection();
        ResultSet rs=this.dao.executeQuery(sql);
        try{
            while(rs.next()){
                int id=rs.getInt("id");
                String name=rs.getString("name");
                View node=new View();
                node.setId(id);
                node.setName(name);
                views.add(node);
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        dao.closeConnection();
        return views;
    }

    public Collection<View> getViews(int[] viewids) {
        Collection<View> views = null;
        StringBuffer sql = new StringBuffer("select views.*, rules.* from views, rules, r_v" +
                " where views.id=r_v.vid and rules.id=r_v.rid and views.id in (");
        for (int v : viewids) {
            sql.append(v);
            sql.append(",");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(") order by views.id");
        dao.openConnection();
        ResultSet rs=this.dao.executeQuery(sql.toString());
        try{
            HashMap<String, View> viewHash = new HashMap<String, View>();
            while(rs.next()){
                int id=rs.getInt("id");
                String name=rs.getString("name");
                View node = viewHash.get(name);
                if (node == null) {
                    node = new View();
                    node.setId(id);
                    node.setName(name);
                    viewHash.put(name, node);
                } 
                Rule r = new Rule();
                Herb herb = new Herb();
                herb.setName(rs.getString("herb"));
                Unit ounit = new Unit();
                ounit.setQuantity(rs.getFloat("quantity"));
                ounit.setUnit(rs.getString("aunit"));
                herb.setDosage(ounit);
                r.setHerb(herb);
                Unit unit = new Unit();
                unit.setQuantity(rs.getFloat("quantity"));
                unit.setUnit(rs.getString("bunit"));
                r.setUnit(unit);
                node.addRule(herb, r);
            }
            views = viewHash.values();
        }catch(Exception e){
            e.printStackTrace();
        }
        dao.closeConnection();
        return views;
    }

    public View getView(int viewid) {
        StringBuffer sql = new StringBuffer("select views.*, rules.* from views, rules, r_v" +
                " where views.id=r_v.vid and rules.id=r_v.rid and views.id=");
        sql.append(viewid);
        dao.openConnection();
        ResultSet rs=this.dao.executeQuery(sql.toString());
        View node = null;
        try{
            node = new View();
            node.setId(viewid);
            boolean hasRules = false;
            
            while(rs.next()){
                hasRules = true;
                String name=rs.getString("name");
                node.setName(name);
                Rule r = new Rule();
                r.setId(rs.getInt(3));
                Herb herb = new Herb();
                String hn = rs.getString("herb");
                if (hn == null || hn.trim().equals("")) {
                    hn = "";
                }
                herb.setName(hn);
                Unit ounit = new Unit();
                ounit.setQuantity(rs.getFloat("quantity"));
                ounit.setUnit(rs.getString("aunit"));
                herb.setDosage(ounit);
                r.setHerb(herb);
                Unit unit = new Unit();
                unit.setQuantity(rs.getFloat("quantity"));
                unit.setUnit(rs.getString("bunit"));
                r.setUnit(unit);
                node.addRule(herb, r);
            }
            if (!hasRules) {
                sql.setLength(0);
                sql.append("select * from views where id=");
                sql.append(viewid);
                rs=this.dao.executeQuery(sql.toString());
                while (rs.next()) {
                    String name=rs.getString("name");
                    node.setName(name);
                }
            }
            if (node.getName() == null) {
                node = null;
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        dao.closeConnection();
        return node;
    }

    public View addView(String name) {
        dao.openConnection();
        String sql = "insert into views(id, name) values(null, '" + name + "')";
        int num = dao.executeUpdate(sql);
        View v = null;
        if (num > 0) {
            v = new View();
            sql = "select id from views where name='" + name +"'";
            ResultSet rs = dao.executeQuery(sql);
            try {
                while (rs.next()) {
                    int id = rs.getInt(1);
                    v.setId(id);
                    v.setName(name);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        dao.execute("shutdown");
        dao.closeConnection();
        return v;
    }
    
    public int addRule(String unit, String quantity, String herbName) {
        dao.openConnection();
        StringBuilder sql = new StringBuilder();
        sql.append("insert into rules(id, bunit, aunit, quantity, herb) values(null,'克','");
        sql.append(unit);
        sql.append("',");
        sql.append(quantity);
        sql.append(",'");
        sql.append(herbName);
        sql.append("')");
        int num = dao.executeUpdate(sql.toString());
        int id = -1;
        if (num > 0) {
            String sqls = "select identity() from rules";
            ResultSet rs = dao.executeQuery(sqls);
            try {
                while (rs.next()) {
                    id = rs.getInt(1);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        dao.execute("shutdown");
        dao.closeConnection();
        return id;
    }
    
    
    public View getView(String name) {
        String sql = "select id from views where name='" + name +"'";
        dao.openConnection();
        ResultSet rs = dao.executeQuery(sql);
        View v = null;
        try {
            while (rs.next()) {
                v = new View();
                int id = rs.getInt(1);
                v.setId(id);
                v.setName(name);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        dao.closeConnection();
        return v;
    }
    
    public View deleteView(int id) {
        View view = getView(id);
        dao.openConnection();
        String sql = "delete from r_v where vid=" + id;
        dao.execute(sql);
        sql = "delete from views where id=" + id;
        dao.executeUpdate(sql);
        if (view != null && view.getRules().size() > 0) {
            HashMap<Herb, Rule> rules = view.getRules();
            if (rules.size() > 0) {
                StringBuilder sb = new StringBuilder();
                sb.append("delete from rules where id in(");
                for (Rule rule : rules.values()) {
                    sb.append("'");
                    sb.append(rule.getId());
                    sb.append("',");
                }
                sb.deleteCharAt(sql.length() - 1);
                sb.append(")");
                dao.executeUpdate(sb.toString());
            }
        }
        dao.closeConnection();
        return view;
    }

    public int addRule(String viewid, String unit, String quantity, String herbName) {
        int id = addRule(unit, quantity, herbName);
        if (id != -1) {
            addRV(viewid, id);
        }
        return id;
    }

    private void addRV(String viewid, int id) {
        dao.openConnection();
        String sql = "insert into r_v(id, rid, vid) values(null," + id + "," + viewid +")";
        dao.executeUpdate(sql);
        dao.execute("shutdown");
        dao.closeConnection();
    }

    public Rule deleteRule(String viewid, String ruleid) {
        Rule r = getRule(ruleid);
        deleteRV(viewid, ruleid);
        deleteRule(ruleid);
        return r;
    }

    private void deleteRule(String ruleid) {
        dao.openConnection();
        String sql = "delete from rules where id="+ruleid;
        dao.executeUpdate(sql);
        dao.closeConnection();
        
    }

    private void deleteRV(String viewid, String ruleid) {
        dao.openConnection();
        String sql = "delete from r_v where rid="+ruleid + " and vid=" + viewid;
        dao.executeUpdate(sql);
        dao.closeConnection();
    }

    private Rule getRule(String ruleid) {
        String sql="select * from rules where id=" + ruleid;
        dao.openConnection();
        ResultSet rs=this.dao.executeQuery(sql);
        Rule r = null;
        try{
            while(rs.next()){
                r = new Rule();
                Herb herb = new Herb();
                herb.setName(rs.getString("herb"));
                Unit ounit = new Unit();
                ounit.setQuantity(rs.getFloat("quantity"));
                ounit.setUnit(rs.getString("aunit"));
                herb.setDosage(ounit);
                r.setHerb(herb);
                r.setId(rs.getInt("id"));
                Unit unit = new Unit();
                unit.setQuantity(rs.getFloat("quantity"));
                unit.setUnit(rs.getString("bunit"));
                r.setUnit(unit);
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        dao.closeConnection();
        return r;
    }

    public Rule updateRule(String ruleid, String unit, String quantity, String herbName) {
        dao.openConnection();
        String sql = "update rules set aunit='"+unit+"', quantity=" +quantity +", herb='"+herbName+"' where id="+ruleid;
        dao.executeUpdate(sql);
        dao.closeConnection();
        Rule r = getRule(ruleid);
        return r;
    }

    public View updateView(String id, String n) {
        dao.openConnection();
        String sql = "update views set name='"+n+"' where id="+id;
        dao.executeUpdate(sql);
        dao.closeConnection();
        View v = getView(Integer.parseInt(id));
        return v;
    }


}